﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TransCend.DAL;
using TransCend.Model;

namespace TransCend.DB
{
    /// <summary>
    /// 解析出Model的各种SQL语句
    /// </summary>
    public class ModelSQLUtil
    {
        private ModelProperty bp = null;
        private string insertSQL = null;
        //private SqlParameter[] insertSqlParameter = null;
        private List<Column> insertColumns = null;
        private string updateSQL = null;
        //private SqlParameter[] updateSQLParameter = null;
        private List<Column> updateColumns = null;
        private string selectSQL = null;
        private string selectCountSQL = null;
        private string deleteSQL = null;

        public ModelSQLUtil(ModelProperty bp)
        {
            this.bp = bp;
        }

        public string GetSelectSQL()
        {
            if (selectSQL == null)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                for (int i = 0; i < bp.GetColumns().Count; i++)
                {
                    Column c = bp.GetColumns()[i];
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append(c.Name).Append(" as ").Append(c.Alias);
                }
                sb.Append(" FROM ").Append(bp.GetTableName()).Append(GetWithNoLock());
                selectSQL = sb.ToString();
            }
            return selectSQL;
        }

        /// <summary>
        /// 获取count查询的Sql
        /// </summary>
        /// <returns></returns>
        public string GetSelectCountSQL()
        {
            if (selectCountSQL == null) {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                sb.Append("count(*)");
                sb.Append(" FROM ").Append(bp.GetTableName()).Append(GetWithNoLock());
                selectCountSQL = sb.ToString();
            }
            return selectCountSQL;
        }

        private List<Column> GetInsertColumns()
        {
            if (insertColumns == null)
            {
                insertColumns = bp.GetInsertColumns();
            }
            return insertColumns;
        }

        public string GetInsertSQL()
        {
            if (insertSQL == null)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("INSERT INTO ");
                sb.Append(bp.GetTableName());
                sb.Append("(");
                List<Column> columns = GetInsertColumns();
                for (int i = 0; i < columns.Count; i++)
                {
                    Column c = columns[i];
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append(c.Name);
                }
                sb.Append(") VALUES(");
                for (int i = 0; i < columns.Count; i++)
                {
                    Column c = columns[i];
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append("@").Append(c.Alias);
                }
                sb.Append(")");
                sb.Append(GetModelProperty().GetPrimaryKey().Identity ? ";select @@IDENTITY" : "select 1");
                insertSQL = sb.ToString();
            }
            
            return insertSQL ;
        }

        public SqlParameter[] GetInsertSqlParameters<T>(T m) where T : BaseModel
        {

            List<Column> columns = GetInsertColumns();
            SqlParameter[] insertSqlParameter = new SqlParameter[columns.Count];
            for (int i = 0; i < columns.Count; i++)
            {
                Column c = columns[i];
                insertSqlParameter[i] = GetSqlParameter(c, m);
            }
            
            return insertSqlParameter;
        }

        public SqlDbType GetSqlDbType(Column c)
        {
            SqlDbType sqlType = SqlDbType.NVarChar;
            switch (c.Type)
            {
                case DataType.Int: sqlType = SqlDbType.Int; break;
                case DataType.Date: sqlType = SqlDbType.Date; break;
                case DataType.DateTime: sqlType = SqlDbType.DateTime; break;
                case DataType.Decimal: sqlType = SqlDbType.Decimal; break;
                case DataType.NText: sqlType = SqlDbType.NText; break;
                case DataType.Float: sqlType = SqlDbType.Float; break;
                case DataType.Bit: sqlType = SqlDbType.Bit; break;
                case DataType.Char: sqlType = SqlDbType.Char; break;
            }
            return sqlType;
        }

        public SqlParameter GetSqlParameter<T>(Column c, T m) where T : BaseModel
        {
            SqlDbType dt = GetSqlDbType(c);
            SqlParameter pp = new SqlParameter("@" + c.Alias, dt);
            pp.Value = m.ContainsKey(c.Alias) && m[c.Alias] != null ? GetModelAsValue<T>(m, c.Alias, dt) : c.AutoGenerateAttribute != null ? c.AutoGenerateAttribute.Generator.Generate() : c.Default != null ? c.Default : DBNull.Value;
            return pp;
        }

        public object GetModelAsValue<T>(T m, string alias, SqlDbType t) where T : BaseModel
        {
            object obj = null;
            switch (t)
            {
                case SqlDbType.Int: obj = m.GetInt(alias); break;
                case SqlDbType.Date: obj = m.GetDateTime(alias); break;
                case SqlDbType.DateTime: obj = m.GetDateTime(alias); break;
                case SqlDbType.Decimal: obj = m.GetDecimal(alias); break;
                case SqlDbType.Float: obj = m.GetDouble(alias); break;
                case SqlDbType.Bit: obj = m.GetBit(alias); break;
                default: obj = m.GetString(alias); break;
            }
            return obj == null ? DBNull.Value : obj;
        }

        public SqlParameter GetIDSqlParameter<T>(T m) where T : BaseModel
        {
            return GetSqlParameter(bp.GetPkColumn(), m);
        }

        private List<Column> GetUpdateColumns()
        {
            if (updateColumns == null)
            {
                updateColumns = bp.GetUpdateColumns();
            }
            return updateColumns;
        }

        public string GetWithNoLock()
        {
            return " WITH(NOLOCK) ";
        }

        public string GetWithRowLock()
        {
            return " WITH(ROWLOCK) ";
        }

        public string GetUpdateSQL()
        {
            if (updateSQL == null)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("UPDATE ");
                sb.Append(bp.GetTableName()).Append(GetWithRowLock());
                sb.Append(" SET ");
                List<Column> columns = GetUpdateColumns();
                for (int i = 0; i < columns.Count; i++)
                {
                    Column c = columns[i];
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append(" ").Append(c.Name).Append("=@").Append(c.Alias);
                }
                sb.Append(" WHERE ").Append(bp.GetPk()).Append("=@").Append(bp.GetPkColumn().Alias);
                updateSQL = sb.ToString();
            }
            return updateSQL;
        }

        public SqlParameter[] GetUpdateSQLParameter<T>(T m) where T : BaseModel
        {

            List<Column> columns = GetUpdateColumns();
            SqlParameter[] updateSQLParameter = new SqlParameter[columns.Count + 1];
            for (int i = 0; i < columns.Count; i++)
            {
                Column c = columns[i];
                updateSQLParameter[i] = GetSqlParameter(c, m);
            }
            updateSQLParameter[columns.Count] = GetSqlParameter(bp.GetPkColumn(), m);
            
            return updateSQLParameter;
        }

        public string GetDeleteSQL()
        {
            if (deleteSQL == null)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("DELETE FROM ");
                sb.Append(bp.GetTableName()).Append(GetWithRowLock());
                sb.Append(" WHERE ");
                sb.Append(bp.GetPk());
                sb.Append(" = @ID");
                deleteSQL = sb.ToString();
            }
            return deleteSQL;
        }

        public ModelProperty GetModelProperty()
        {
            return bp;
        }
    }
}
